<?php

class search {

	private $userObj;
	private $_outputTemplate;
	private $_rawResult;
	private $m_db;
	private $_libraryQueries;
	
	private $m_chartQueries;
	private $m_location;
	private $m_bookQueries;
	private $m_imageQuery;
	private $m_imageSetQuery;
	private $m_communitiesQuery;
	
	private $_selectedSQL;
	private $_outputSQL = array();
	private $_globalQueries;
	private $_localOnly;
	private $_searchTerm;

	public function __construct(user &$userObj){
		$this->m_db = db::singleton();
		$this->userObj = $userObj;
		
		$this->_libraryQueries = array(
			"book" => "SELECT 'book' AS type, bID, name, description FROM %s.book WHERE book.user_uID=%d AND(book.name LIKE '%%%s%%' OR book.description LIKE '%%%3\$s%%')",
			"chart" => "SELECT 'chart' AS type, chID, name, description FROM %s.chart WHERE user_uID=%d AND (name LIKE '%%%s%%' OR description LIKE '%%%3\$s%%')",
			"imageset" => "SELECT 'imageset' AS type, isID, name FROM %s.imageset WHERE user_uID=%d AND (name LIKE '%%%s%%')",
			"groups" => "SELECT 'groups' AS type, gID, name, description FROM %s.group LEFT JOIN %1\$s.group_user ON group.gID=group_user.group_gID WHERE group_user.user_uID=%d AND (name LIKE '%%%s%%' OR description LIKE '%%%3\$s%%')"
		);

		$this->_globalQueries = array(
			"book" => "SELECT 'book' AS type, bID, name, description FROM %s.book WHERE book.private=0 AND(book.name LIKE '%%%s%%' OR book.description LIKE '%%%2\$s%%')",
			"chart" => "SELECT 'chart' AS type, chID, name, description FROM %s.chart WHERE private=0 AND (name LIKE '%%%s%%' OR description LIKE '%%%2\$s%%')",
			"imageset" => "SELECT 'imageset' AS type, isID, name FROM %s.imageset WHERE private=0 AND (name LIKE '%%%s%%')",
			"groups" => "SELECT 'groups' AS type, gID, name, description FROM %s.group WHERE group.private=0 AND (name LIKE '%%%s%%' OR description LIKE '%%%2\$s%%')"								
		);
		
		/*
		
			SELECT 'My Charts' AS library, 'true' as editable, chID, chart.name, style, chart.description, book.name AS bookName, user.uID AS ownerUID, CONCAT(user.firstName, " ", user.lastName) AS ownerName
			FROM picboard.chart 
			LEFT JOIN picboard.book_chart ON chart.chID=book_chart.chart_chID
			LEFT JOIN picboard.book ON book_chart.book_bID=book.bID
			LEFT JOIN picboard.user ON chart.originator_uID=user.uID
			WHERE chart.user_uID=7 AND (chart.name LIKE '%An%' OR chart.description LIKE '%An%');
			
			SELECT group.name AS library, IF(group_user.isAdmin=0, IF(chart.originator_uID=11, true, false), true) as editable, chart.*, group.gID, group_user.isAdmin, group_user.active, group.name, group.description, user.uID AS ownerUID, CONCAT(user.firstName, " ", user.lastName) AS ownerName
			FROM picboard.chart 
			LEFT JOIN picboard.group_user ON chart.group_gID=group_user.group_gID 
			LEFT JOIN picboard.group ON group_user.group_gID=group.gID 
			LEFT JOIN picboard.user ON chart.originator_uID=user.uID
			LEFT JOIN picboard.book_chart ON chart.chID=book_chart.chart_chID
			LEFT JOIN picboard.book ON book_chart.book_bID=book.bID
			WHERE group_user.user_uID = 11 AND chart.name LIKE '%%' OR group_user.user_uID = 11 AND chart.description LIKE '%%';		
		*/
		
		$this->m_chartQueries = array(
			"mine" => "SELECT 'My Charts' AS library, 'true' as editable, chID, chart.name, chart.thumbnail, style, chart.description, book.name AS bookName, user.userName AS ownerName, user.uID AS ownerUID FROM %s.chart LEFT JOIN %1\$s.book_chart ON chart.chID=book_chart.chart_chID LEFT JOIN %1\$s.book ON book_chart.book_bID=book.bID LEFT JOIN %1\$s.user ON chart.originator_uID=user.uID WHERE chart.user_uID=%2\$d AND (chart.name LIKE '%%%3\$s%%' OR chart.description LIKE '%%%3\$s%%');",
			"group" => "SELECT group.name AS library, IF(group_user.isAdmin=0, IF(chart.originator_uID=%2\$d, true, false), true) as editable, chart.*, group.gID, group_user.isAdmin, group_user.active, group.name, group.description, user.uID AS ownerUID, user.userName AS ownerName FROM %1\$s.chart LEFT JOIN %1\$s.group_user ON chart.group_gID=group_user.group_gID LEFT JOIN %1\$s.group ON group_user.group_gID=group.gID LEFT JOIN %1\$s.user ON chart.originator_uID=user.uID LEFT JOIN %1\$s.book_chart ON chart.chID=book_chart.chart_chID LEFT JOIN %1\$s.book ON book_chart.book_bID=book.bID WHERE group_user.user_uID = %2\$d AND chart.name LIKE '%%%3\$s%%' AND group_user.active = 1 OR group_user.user_uID = %2\$d AND chart.description LIKE '%%%3\$s%%' AND group_user.active = 1;",
			"public" => "SELECT 'Public' AS library, 'false' as editable, chart.* FROM %s.chart WHERE chart.user_uID IS NULL AND chart.group_gID IS NULL AND chart.name LIKE '%%%3\$s%%'"
		);
		
		/*
			SELECT group.name AS library, IF(group_user.isAdmin=0, IF(book.originator_uID=7, true, false), true) as editable, group.gID, group_user.isAdmin, group_user.active, group.name, group.description, user.uID AS ownerUID, CONCAT(user.firstName, ' ', user.lastName) AS ownerName
			FROM picboard.book 
			LEFT JOIN picboard.group_user ON book.group_gID=group_user.group_gID 
			LEFT JOIN picboard.group ON group_user.group_gID=group.gID 
			LEFT JOIN picboard.user ON book.originator_uID=user.uID
			WHERE group_user.user_uID = 7 AND book.name LIKE '%An%' AND group_user.active = 1 OR group_user.user_uID = 7 AND book.description LIKE '%An%' AND group_user.active = 1;		
		
		
		*/
		
		$this->m_bookQueries = array(
			"mine" => "SELECT 'My Books' AS library, 'true' as editable, bID, name, description, user.uID AS ownerUID, user.userName AS ownerName FROM %s.book LEFT JOIN %1\$s.user ON book.originator_uID=user.uID WHERE book.user_uID=%d AND (name LIKE '%%%3\$s%%' OR description LIKE '%%%3\$s%%');",
			"group" => "SELECT group.name AS library, IF(group_user.isAdmin=0, IF(book.originator_uID=%2\$d, true, false), true) as editable, group.gID, group_user.isAdmin, group_user.active, group.name, group.description, user.uID AS ownerUID, user.userName AS ownerName FROM %1\$s.book  LEFT JOIN %1\$s.group_user ON book.group_gID=group_user.group_gID LEFT JOIN %1\$s.group ON group_user.group_gID=group.gID LEFT JOIN %1\$s.user ON book.originator_uID=user.uID WHERE group_user.user_uID = %2\$d AND book.name LIKE '%%%3\$s%%' AND group_user.active = 1 OR group_user.user_uID = %2\$d AND book.description LIKE '%%%3\$s%%' AND group_user.active = 1;",
			"public" => "SELECT 'Public' AS library, 'false' as editable, book.* FROM %s.book WHERE user_uID IS NULL AND group_gID IS NULL AND name LIKE '%%%3\$s%%'"		
		);
		
		/*
		
			SELECT imageset.name AS album, 'My Images' AS library, '' AS community, 'true' as editable, image.*, user.uID AS ownerUID, CONCAT(user.firstName, ' ', user.lastName) AS ownerName
			FROM picboard.image
			LEFT JOIN picboard.image_imageset ON image.imgID=image_imageset.image_imgID
			LEFT JOIN picboard.imageset ON imageset.isID=image_imageset.imageset_isID
			LEFT JOIN picboard.user ON user.uID = imageset.user_uID
			WHERE imageset.user_uID = 7 AND image.name LIKE '%Sc%'	
			
			SELECT imageset.name AS library, IF(group_user.isAdmin=0, false, true) as editable, image.*, originator.uID AS ownerUID, group.gID AS ownerGID, institution.name as instName, CONCAT(originator.firstName, ' ', originator.lastName) AS ownerName
			FROM picboard.image
			LEFT JOIN picboard.image_imageset ON image.imgID=image_imageset.image_imgID
			LEFT JOIN picboard.imageset ON imageset.isID=image_imageset.imageset_isID
			LEFT JOIN picboard.group ON imageset.group_gID = group.gID
			LEFT JOIN picboard.group_user ON group.gID=group_user.group_gID
			LEFT JOIN picboard.user ON user.uID = group_user.user_uID
			LEFT JOIN picboard.user AS originator ON user.uID = image.originator_uID
			LEFT JOIN picboard.institution ON group.institution_instID = institution.instID
			WHERE group_user.user_uID = 1 AND group_user.active = 1 AND image.name LIKE '%%';
		*/
		
		$this->m_imageQuery = array(
			"mine" => "SELECT imageset.name AS album, 'My Images' AS library, '' AS community, 'true' as editable, image.*, user.uID AS ownerUID, user.userName AS ownerName FROM %1\$s.image LEFT JOIN %1\$s.image_imageset ON image.imgID=image_imageset.image_imgID LEFT JOIN %1\$s.imageset ON imageset.isID=image_imageset.imageset_isID LEFT JOIN %1\$s.user ON user.uID = imageset.user_uID WHERE imageset.user_uID = %2\$d AND image.name LIKE '%%%3\$s%%'",
			"group" => "SELECT imageset.name AS album, group.name AS library, institution.name as community, IF(group_user.isAdmin=0, IF(image.originator_uID=%2\$d, true, false), true) as editable, image.*, originator.uID AS ownerUID, group.gID AS ownerGID, originator.userName AS ownerName FROM %1\$s.image LEFT JOIN %1\$s.image_imageset ON image.imgID=image_imageset.image_imgID LEFT JOIN %1\$s.imageset ON imageset.isID=image_imageset.imageset_isID LEFT JOIN %1\$s.group ON imageset.group_gID = group.gID LEFT JOIN %1\$s.group_user ON group.gID=group_user.group_gID LEFT JOIN %1\$s.institution ON group.institution_instID = institution.instID LEFT JOIN %1\$s.user ON user.uID = group_user.user_uID LEFT JOIN %1\$s.user AS originator ON user.uID = image.originator_uID WHERE group_user.user_uID = %2\$d AND group_user.active = 1 AND image.name LIKE '%%%3\$s%%';"
		);

		/*
			SELECT imageset.name AS album, 'My Images' AS library, '' AS community, 'true' as editable, user.uID AS ownerUID, CONCAT(user.firstName, ' ', user.lastName) AS ownerName
			FROM picboard.imageset
			LEFT JOIN picboard.user ON user.uID = imageset.user_uID
			WHERE imageset.user_uID = 7 AND imageset.name LIKE '%Sc%'	
			
			SELECT imageset.name AS library, IF(group_user.isAdmin=0, false, true) as editable, originator.uID AS ownerUID, group.gID AS ownerGID, institution.name as instName, CONCAT(originator.firstName, ' ', originator.lastName) AS ownerName
			FROM picboard.imageset
			LEFT JOIN picboard.group ON imageset.group_gID = group.gID
			LEFT JOIN picboard.group_user ON group.gID=group_user.group_gID
			LEFT JOIN picboard.user ON user.uID = group_user.user_uID
			LEFT JOIN picboard.user AS originator ON user.uID = imageset.originator_uID
			LEFT JOIN picboard.institution ON group.institution_instID = institution.instID
			WHERE group_user.user_uID = 1 AND group_user.active = 1 AND imageset.name LIKE '%%';
		*/
		
		$this->m_imageSetQuery = array(
			"mine" => "SELECT imageset.name AS album, 'My Images' AS library, '' AS community, 'true' as editable, imageset.isID, user.uID AS ownerUID, user.userName AS ownerName FROM %1\$s.imageset LEFT JOIN %1\$s.user ON user.uID = imageset.user_uID WHERE imageset.user_uID = %2\$d AND imageset.name LIKE '%%%3\$s%%'",
			"group" => "SELECT imageset.name AS album, group.name AS library, IF(group_user.isAdmin=0, 'false', 'true') as editable, originator.uID AS ownerUID, group.gID AS ownerGID, imageset.isID, institution.name as instName, originator.userName AS ownerName FROM %1\$s.imageset LEFT JOIN %1\$s.group ON imageset.group_gID = group.gID LEFT JOIN %1\$s.group_user ON group.gID=group_user.group_gID LEFT JOIN %1\$s.user ON user.uID = group_user.user_uID LEFT JOIN %1\$s.user AS originator ON imageset.originator_uID = originator.uID LEFT JOIN %1\$s.institution ON group.institution_instID = institution.instID WHERE group_user.user_uID = %2\$d AND group_user.active = 1 AND imageset.name LIKE '%%%3\$s%%';"
		);
		
		/*
			SELECT group.gID, group.name, group.description, institution.name AS instName, admin.userName AS adminName, group_user.active AS hasMembership, group.byInvitation AS invitationOnly, group_user.isAdmin AS userisAdmin
			FROM picboard.group
			LEFT JOIN picboard.group_user ON group.gID=group_user.group_gID AND group_user.user_uID=7
			LEFT JOIN picboard.institution ON group.institution_instID = institution.instID
			LEFT JOIN picboard.group_user AS admLink ON group.gID=admLink.group_gID AND admLink.isAdmin=1
			LEFT JOIN picboard.user AS admin ON admin.uID = admLink.user_uID
			WHERE (group_user.active=1 OR group.byInvitation=0) AND (group.name LIKE '%%' OR group.description LIKE '%%') AND group.private = 0 OR group_user.active = 1
			GROUP BY gID;	
		*/
		
		// Communities
		/*
			SELECT institution.instID, institution.name, institution.description, admin.userName AS adminName, institution_user.active AS hasMembership, institution_user.admin AS userisAdmin
			FROM picboard.institution
			LEFT JOIN picboard.institution_user ON institution.instID=institution_user.institution_instID AND institution_user.user_uID=7
			LEFT JOIN picboard.institution_user AS admLink ON institution.instID=admLink.institution_instID AND admLink.admin=1
			LEFT JOIN picboard.user AS admin ON admin.uID = admLink.user_uID
			WHERE institution.instID != 1 AND (institution.name LIKE '%%' OR institution.description LIKE '%%') AND institution_user.admin = 1;			
		*/
		
		$this->m_communitiesQuery = array(
			"groups" => "SELECT group.gID, group.name, group.description, institution.name AS instName, admin.userName AS adminName, group_user.active AS hasMembership, group.byInvitation AS invitationOnly, group_user.isAdmin AS userisAdmin FROM %1\$s.group LEFT JOIN %1\$s.group_user ON group.gID=group_user.group_gID AND group_user.user_uID=%2\$d LEFT JOIN %1\$s.institution ON group.institution_instID = institution.instID LEFT JOIN %1\$s.group_user AS admLink ON group.gID=admLink.group_gID AND admLink.isAdmin=1 LEFT JOIN %1\$s.user AS admin ON admin.uID = admLink.user_uID WHERE (group_user.active=1 OR group.byInvitation=0) AND (group.name LIKE '%%%3\$s%%' OR group.description LIKE '%%%3\$s%%') AND institution.private=0 GROUP BY gID",
			"communities" => "SELECT institution.instID, institution.name, institution.description, admin.userName AS adminName, institution_user.active AS hasMembership, institution_user.admin AS userisAdmin FROM %1\$s.institution LEFT JOIN %1\$s.institution_user ON institution.instID=institution_user.institution_instID AND institution_user.user_uID=%2\$d LEFT JOIN %1\$s.institution_user AS admLink ON institution.instID=admLink.institution_instID AND admLink.admin=1 LEFT JOIN %1\$s.user AS admin ON admin.uID = admLink.user_uID WHERE institution.instID != 1 AND (institution.name LIKE '%%%3\$s%%' OR institution.description LIKE '%%%3\$s%%')  AND institution_user.admin = 1"
		);
		
	}

	public function setLocation($loc){
		$this->_localOnly = ($loc == "myLibrary") ? true : false;
		$this->m_location = $loc;
	}
	
	public function setContent($content){
		if(isset($this->_localOnly) == false) throw new Exception("Please select a search location before setting the content type.");
		
		switch($content){

			case "allTypes":
				$this->_selectedSQL = ($this->_localOnly) ? $this->_libraryQueries : $this->_globalQueries;
			break;
			
			case "charts":
				$this->_selectedSQL['chart'] = ($this->_localOnly) ? $this->_libraryQueries['chart'] : $this->_globalQueries['chart'];
			break;
		
			case "books":
				$this->_selectedSQL['book'] = ($this->_localOnly) ? $this->_libraryQueries['book'] : $this->_globalQueries['book'];
			break;
			
			case "groups":
				$this->_selectedSQL['groups'] = ($this->_localOnly) ? $this->_libraryQueries['groups'] : $this->_globalQueries['groups'];
			break;
			
			case "imagesets":
				//$this->_selectedSQL = ($this->_localOnly) ? array($this->_libraryQueries['imageset'], $this->_libraryQueries['image']) : array($this->_globalQueries['imageset'], $this->_globalQueries['image']);
				$this->_selectedSQL = ($this->_localOnly) ? array($this->_libraryQueries['imageset']) : array($this->_globalQueries['imageset']);
			break;
		
		}
	}
	
	public function terms($searchTerms){
		$this->_searchTerm = $searchTerms;
	}
	
	public function setQueryFormat($format){
		$this->_queryFormat = $format;
	}
			
	public function setResultFormat(template &$template){
		$this->_outputTemplate = $template;
	}
			
	public function fetch(){
		$this->buildQuery();
		$results = $this->executeQuery();
		
		return $this->formatResults($results);
	}
	
	public function searchCharts(){
		switch($this->m_location){
			
			case "charts":
				// My charts
				$query = sprintf($this->m_chartQueries['mine'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			case "groups":
				// My groups
				$query = sprintf($this->m_chartQueries['group'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			case "public":
				$query = sprintf($this->m_chartQueries['public'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			default:
				// All
				foreach($this->m_chartQueries as $sql) $this->m_db->queuedQuery( sprintf($sql, SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm) );
				$res = $this->m_db->runBatch();
				
				$results = array();
				
				foreach($res as $resultset){
					$results = array_merge($results, $resultset);
				}
				
				return $results;
			break;
			
		}
	}
	
	public function searchBooks(){
		switch($this->m_location){
			case "charts":
				// My charts
				$query = sprintf($this->m_bookQueries['mine'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			case "groups":
				// My groups
				$query = sprintf($this->m_bookQueries['group'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			case "public":
				$query = sprintf($this->m_bookQueries['public'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			default:
				// All
				foreach($this->m_bookQueries as $sql) $this->m_db->queuedQuery( sprintf($sql, SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm) );
				$res = $this->m_db->runBatch();
				
				$results = array();
				
				foreach($res as $resultset){
					$results = array_merge($results, $resultset);
				}
				
				return $results;
			break;		
		}
	}

	public function searchImages(){
		switch($this->m_location){
			
			case "charts":
				// My charts
				$query = sprintf($this->m_imageQuery['mine'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			case "groups":
				// My groups
				$query = sprintf($this->m_imageQuery['group'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			default:
				// All
				foreach($this->m_imageQuery as $sql) $this->m_db->queuedQuery( sprintf($sql, SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm) );
				$res = $this->m_db->runBatch();
				
				$results = array();
				
				foreach($res as $resultset){
					$results = array_merge($results, $resultset);
				}
				
				return $results;
			break;
			
		}
	}
	
	public function searchImagesets(){
		switch($this->m_location){
			
			case "charts":
				// My charts
				$query = sprintf($this->m_imageSetQuery['mine'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			case "groups":
				// My groups
				$query = sprintf($this->m_imageSetQuery['group'], SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm);
				return $this->m_db->single($query);
			break;
			
			default:
				// All
				foreach($this->m_imageSetQuery as $sql) $this->m_db->queuedQuery( sprintf($sql, SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm) );
				$res = $this->m_db->runBatch();
				
				$results = array();
				
				foreach($res as $resultset){
					$results = array_merge($results, $resultset);
				}
				
				return $results;
			break;
			
		}
	}
	
	public function searchCommunities(){
		// All
		foreach($this->m_communitiesQuery as $sql) $this->m_db->queuedQuery( sprintf($sql, SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm) );
		$res = $this->m_db->runBatch();
		
		$results = array();
		
		foreach($res as $resultset){
			array_push($results, $resultset);
		}
		
		return $results;		
	}
	
	private function buildQuery(){
		if($this->_localOnly){
			foreach($this->_selectedSQL as &$query){
				array_push($this->_outputSQL, sprintf($query, SYS_DB_DBNAME, $this->userObj->getuID(), $this->_searchTerm) );
			}
		} else {
			foreach($this->_selectedSQL as &$query){
				array_push($this->_outputSQL, sprintf($query, SYS_DB_DBNAME, $this->_searchTerm) );
			}		
		}
	}
	
	private function executeQuery(){
		foreach($this->_outputSQL as $sql) $this->m_db->queuedQuery($sql);
		
		$res = $this->m_db->runBatch();
		return $res;
	}

	private function formatResults($data){
		$area = array_keys($this->_libraryQueries);
		
		foreach($data as $query){
			foreach($query as $result){

				switch($result['type']){
			
					case "book":
						$out = "<a href='book?id=" . $result['bID'] . "'>" . $result['name'] . "</a>";
						$out .= ($result['description'] != "") ? "<p>" . $result['description'] . "</p>" : "<p class='smgreyText'>No description</p>"; 
					break; 
					
					case "chart":
						$out = "<a href='chart?id=" . $result['chID'] . "'>" . $result['name'] . "</a>";
						$out .= ($result['description'] != "") ? "<p>" . $result['description'] . "</p>" : "<p class='smgreyText'>No description</p>"; 
					break;
					
					case "imageset":
						$out = "<a href='imagesets?id=" . $result['isID'] . "'>" . $result['name'] . "</a>";
					break;
					
					case "groups":
						$out = "<a href='group?id=" . $result['gID'] . "'>" . $result['name'] . "</a>";
						$out .= ($result['description'] != "") ? "<p>" . $result['description'] . "</p>" : "<p class='smgreyText'>No description</p>"; 											
					break;
					
				}
				
				$this->_outputTemplate->replaceAll(array(
					"image" => "",
					"desc" => $out
				));				
				
				$this->_rawResult .= $this->_outputTemplate->get();
				$this->_outputTemplate->reset();
			
			}
		
		}
		
		return $this->_rawResult;
	}
	

}

?>